package service.sql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/*
 * Sql
 *
 * Descripción: Esta clase se encarga de los eventos o consultas a la base de datos
 *
 * Autor: Erik
 *
 * Versión: 0.1
 *
 * Fecha: 01/04/14
 *
 */
public class Sql {

    ConectaDb db = null;

    public Sql() {
        db = new ConectaDb();
    }

    public String ejecuta(
            LinkedList sql // viene INSERT, DELETE o UPDATE
            ) {

        String result = null;
        Connection cn = db.getConnection();
        int ctos = 0;
        try {
            Statement st = cn.createStatement();
            Iterator i = sql.iterator();
            int cont = 2;
            while (i.hasNext()) {
                String o = "";

                try {

                    o = i.next().toString();
                    ctos = st.executeUpdate(o);
                } catch (SQLException e) {
                    System.out.println("" + cont + " razon:" + e.getMessage() + "  " + o);
                }
                cont++;
            }

            if (ctos == 0) {
                result = "0 filas afectadas";
            }

        } catch (SQLException e) {
            result = e.getMessage();
        } finally {
            try {
                cn.close();
            } catch (SQLException e) {
                result = e.getMessage();
            }
        }

        return result;
    }

    // ejecuta INS, DEL y UPD
    public String ejecuta(
            String sql // viene INSERT, DELETE o UPDATE
            ) {

        String result = null;
        Connection cn = db.getConnection();

        try {
            Statement st = cn.createStatement();
            int ctos = st.executeUpdate(sql);

            if (ctos == 0) {
                result = "0 filas afectadas";
            }

        } catch (SQLException e) {
            result = e.getMessage();
            System.out.println("error " + e.getMessage());
        } finally {
            try {
                cn.close();
            } catch (SQLException e) {
                result = e.getMessage();
                System.out.println("error " + e.getMessage());
            }
        }

        return result;
    }

    

    public List<Object[]> consulta(String s) {
        List<Object[]> list = null;
        Connection cn = db.getConnection();
        if (cn != null) {
            try {
                Statement st = cn.createStatement();
                ResultSet rs = st.executeQuery(s);
                ResultSetMetaData rm = rs.getMetaData();
                int ctascols = rm.getColumnCount();

                list = new ArrayList<Object[]>();
                while (rs.next()) {
                    Object[] fila = new Object[ctascols];
                    System.out.println("error aca ");
                    for (int i = 0; i < ctascols; i++) {
                        if (fila[i] == null) {
                            fila[i] = "NULL";
                        }
                        fila[i] = rs.getObject(1 + i);
                    }
                    list.add(fila);
                }
                cn.close();
            } catch (SQLException ex) {
                System.out.println("error  " + ex.getMessage());

            }
        }
        return list;
    }

    // ejecuta SELECT
    public Integer[] Paginas(int filsXpag, String Qry) {

        Integer[] result = null;
        Connection cn = null;
        try {
            cn = db.getConnection();
            Statement ps = cn.createStatement();
            ResultSet rs = ps.executeQuery(Qry);

            if (rs.next()) {
                // total filas de consulta
                Integer totalfils = rs.getInt(1);

                // cantidad de paginas con filsXpag
                Integer ctasPags = (totalfils % filsXpag) == 0
                        ? (totalfils / filsXpag)
                        : (totalfils / filsXpag + 1);

                result = new Integer[2];
                result[0] = ctasPags;
                result[1] = totalfils;
            }

        } catch (SQLException e) {
        } finally {
            try {
                cn.close();
            } catch (SQLException e) {
            }
        }

        return result;
    }

    public List<Object[]> consulta2(
            String sql // viene consulta (SELECT ...

            ) {

        List<Object[]> list = null;
        Connection cn = db.getConnection();

        try {
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Object[]>();
            rs.next();
            String s = "select Anho from " + rs.getObject(1).toString() + " group by Anho order by Anho";
            System.out.println(""+s);
            rs = st.executeQuery(s);
            ResultSetMetaData rm = rs.getMetaData();
            int ctascol = rm.getColumnCount();
            
            while (rs.next()) { // tomando la data de la consulta
                Object[] fila = new Object[ctascol];

                for (int i = 0; i < ctascol; i++) {
                    fila[i] = rs.getObject(1 + i);
                }

                list.add(fila);
            }

            if (list.isEmpty()) {
                list = null;
            }
            System.out.println(""+list.size());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }

        return list;
    }

     public List<Object[]> consulta2(
            String sql , // viene consulta (SELECT ...)
            boolean titulos 
            ) {

        List<Object[]> list = null;
        Connection cn = db.getConnection();

        try {
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Object[]>();
            rs.next();
            String s = "select * from V_FORECAST_" + rs.getObject(1).toString() + "_dia ";
            rs = st.executeQuery(s);
            ResultSetMetaData rm = rs.getMetaData();
            int ctascol = rm.getColumnCount();
            
            if (titulos) { // toma titulos o alias de consulta
                String[] titu = new String[ctascol];

                for (int i = 0; i < ctascol; i++) {
                    titu[i] = rm.getColumnLabel(1 + i);
                }

                list.add(titu);
            }
            
            while (rs.next()) { // tomando la data de la consulta
                Object[] fila = new Object[ctascol];

                for (int i = 0; i < ctascol; i++) {
                    fila[i] = rs.getObject(1 + i);
                }

                list.add(fila);
            }

            if (list.isEmpty()) {
                list = null;
            }
            System.out.println(""+list.size());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }

        return list;
    }
    
    public List<Object[]> consulta(
            String sql, // viene consulta (SELECT ...)
            boolean titulos // true==consulta retorna titulo de columnas
            ) {

        List<Object[]> list = null;
        Connection cn = db.getConnection();

        try {
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rm = rs.getMetaData();
            int ctascol = rm.getColumnCount();

            list = new ArrayList<Object[]>();

            if (titulos) { // toma titulos o alias de consulta
                String[] titu = new String[ctascol];

                for (int i = 0; i < ctascol; i++) {
                    titu[i] = rm.getColumnLabel(1 + i);
                }

                list.add(titu);
            }

            while (rs.next()) { // tomando la data de la consulta
                Object[] fila = new Object[ctascol];

                for (int i = 0; i < ctascol; i++) {
                    fila[i] = rs.getObject(1 + i);
                }

                list.add(fila);
            }

            if (list.isEmpty()) {
                list = null;
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }

        return list;
    }

    public Object[] getFila(String sql) {
        List<Object[]> list = consulta(sql, false);
        Object[] fila = null;

        if ((list != null) && (list.size() > 1)) {
            fila = (Object[]) list.get(1);
        }

        return fila;
    }

    public Object getCampo(String sql) {
        Object[] fila = getFila(sql);
        Object campo = null;

        if (fila != null) {
            campo = fila[0];
        }

        return campo;
    }

    // solo para MySQL (consulta paginada)
    public List<Object[]> consulta(String sql,
            boolean titulos, // true==con titulos
            int numpag, // numero de pagina a ver
            int filspag // cantidad de filas por pagina
            ) {

        List<Object[]> list = consulta(
                sql + " LIMIT " + numpag * filspag + "," + filspag,
                titulos);

        return list;
    }

    public List<Object[]> consulta(String Qry, long numpag, long ctasfils) {
        List<Object[]> list = null;
        Connection cn = null;

        try {
            cn = db.getConnection();
            PreparedStatement ps = cn.prepareStatement(Qry);
            ps.setLong(1, (numpag * ctasfils));
            ps.setLong(2, ctasfils);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rm = rs.getMetaData();
            int ctascols = rm.getColumnCount();

            list = new ArrayList<Object[]>();

            while (rs.next()) { // filas de consulta
                Object[] fila = new Object[ctascols];
                for (int i = 0; i < ctascols; i++) {
                    fila[i] = rs.getObject(i + 1);
                }
                list.add(fila);
            }

            if (list.isEmpty()) {
                list = null;
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (SQLException e) {
            }
        }

        return list;
    }
}
